package com.cpa.admin.mapper;

import com.cpa.admin.base.BaseMapper;
import com.cpa.admin.model.UserOrderDo;
import com.cpa.admin.vo.StatisticsMonthOrderVo;
import com.cpa.admin.vo.UserPageVo;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.util.List;

/**
 * @author zhen.zhao01
 * @date 2018/11/10
 */
@Mapper
public interface UserOrderMapper extends BaseMapper<UserOrderDo> {

    /**
     * 根据用户删除order记录
     * @param userId
     */
    @Update("update user_order o set o.delete_flag = 1 where o.user_id = #{userId} and o.delete_flag = 0")
    void deleteByUserId(String userId);

    /**
     * 查找今天的订单
     * @param userId
     * @return
     */
    @Select("select * from user_order o where o.user_id = #{userId}  and o.delete_flag = 0 and to_days(o.create_time) = to_days(now()) ")
    UserOrderDo selectToDay(String userId);


    /**
     * 每月的订单统计
     * @return
     */
    @Select("select * from statistics_month_order")
    List<StatisticsMonthOrderVo> statisticsMonthOrderVo();


    /**
     * 统计用户的推广量
     * @param userId
     * @return
     */
    @Select("select sum(settle_count) " +
            "from user_order o " +
            "       left join user u on o.user_id = u.id " +
            "where o.delete_flag = 0 " +
            "  and o.user_id = #{userId} " +
            "  and o.settle_type = 1  ")
    Integer countUserOrder(String userId);


    /**
     * 统计用户未清理的推广量
     * @param userId
     * @return
     */
    @Select("select sum(uo2.settle_count) " +
            "from user_order uo2 " +
            "       left join user u on u.id = uo2.user_id " +
            "where uo2.delete_flag = 0 " +
            "  and uo2.user_id = #{userId}  " +
            "  and uo2.settle_type = 1 " +
            "  and uo2.create_time > u.clear_amount_date")
    Integer countUnCleanUserOrder(String userId);

    /**
     * 查找到时间但是未结算的订单
     * @return
     */
    @Select(" " +
            "select o.* " +
            "from user_order o " +
            "       left join user u on o.user_id = u.id " +
            "where o.settle_type = 2 and (date(now()) != date(o.create_time) or u.settle_amount_push_time < time(now()))")
    List<UserOrderDo> findUnSettleOrder();


    /**
     * 更新订单状态
     */
    @Update("update user_order o " +
            "left join user u on o.user_id = u.id " +
            "  set o.settle_type = 1 ,o.update_time = current_timestamp " +
            "  where o.settle_type = 2 " +
            "    and (date(now()) != date(o.create_time) or u.settle_amount_push_time < time(now()))")
    void updateUnSettleButTimeOverOrder();


    /**
     * 获取用户后台的table数据
     * @param userId
     * @return
     */
    @Select("select p.product_name as productName,uo.settle_count as settleCount, uo.settle_count * p.product_amount as settleAmount, " +
            "date_format(uo.create_time - interval 1 day,'%Y-%m-%d') as orderDate " +
            "from user_order uo " +
            "       left join product p on p.id = uo.product_id " +
            "where uo.delete_flag = 0 " +
            "  and uo.settle_type = 1 " +
            " and uo.user_id = #{userId} " +
            "order by uo.create_time desc ")
    List<UserPageVo> findUserPageVoList(String userId);


    /**
     * 查找用户的一个月内的订单统计
     * @param userId
     * @return
     */
    @Select("select w.orderDate - interval 1 day AS day, ifnull(uo.settleCount, 0) AS count " +
            "from ((select (curdate() - interval (m.s - 1) day) AS orderDate " +
            "       from (select 1 AS `s` " +
            "             union all select 2 AS `2` " +
            "             union all select 3 AS `3` " +
            "             union all select 4 AS `4` " +
            "             union all select 5 AS `5` " +
            "             union all select 6 AS `6` " +
            "             union all select 7 AS `7` " +
            "             union all select 8 AS `8` " +
            "             union all select 9 AS `9` " +
            "             union all select 10 AS `10` " +
            "             union all select 11 AS `11` " +
            "             union all select 12 AS `12` " +
            "             union all select 13 AS `13` " +
            "             union all select 14 AS `14` " +
            "             union all select 15 AS `15` " +
            "             union all select 16 AS `16` " +
            "             union all select 17 AS `17` " +
            "             union all select 18 AS `18` " +
            "             union all select 19 AS `19` " +
            "             union all select 20 AS `20` " +
            "             union all select 21 AS `21` " +
            "             union all select 22 AS `22` " +
            "             union all select 23 AS `23` " +
            "             union all select 24 AS `24` " +
            "             union all select 25 AS `25` " +
            "             union all select 26 AS `26` " +
            "             union all select 27 AS `27` " +
            "             union all select 28 AS `28` " +
            "             union all select 29 AS `29` " +
            "             union all select 30 AS `30`) `m`) `w` left join " +
            "    (select cast(o.create_time as date) AS createTime, " +
            "            sum(o.settle_count) AS settleCount " +
            "     from cpa_admin_com.user_order o " +
            "     where ((o.delete_flag = 0) " +
            "              and o.settle_type = 1 " +
            "              and o.user_id=#{userId}  " +
            "              and ((curdate() - interval 30 day) < cast(o.create_time as date)) " +
            "              and (cast(o.create_time as date) <= cast(curdate() as date))) " +
            "     group by cast(o.create_time as date)) uo on ((w.orderDate = uo.createTime))) " +
            "order by w.orderDate asc")
    List<StatisticsMonthOrderVo> statisticsUserMonthOrder(String userId);
}
